2 2
Thank you for watching this Financial Modeling lesson! In this extract we will create a stacked column chart showing the trend of revenues and 3 3
their breakdown in each of the years under consideration. 4 4
In the sheet "Column stacked chart" we have a table with items linked to the "P&L" sheet we created earlier. 5 5
These are the various categories of revenue "Generco" has - "Net sales", 6 6
"Other revenues", "Recharges". 7 7
An additional element we have here is the "Gross Margin" percentage for three years. 8 8
Very often it is interesting to combine the analysis of revenue development with one of 9 9
margins, as there is a direct connection between the two. 10 10
Let's select the table and go to "Insert", 11 11
choose "Column" among various chart types 12 12
and select "Stacked column". 13 13
Ok. See how different this chart is from the one the right? 14 14
We'll modify it step by step in order to obtain the same chart. 15 15
First of all, we have to invert rows and columns, as we want to observe the development of revenues per each year. 16 16
Let's do some formatting. 17 17
I prefer to have my charts in "Arial". This is why I'll click over the chart and select its font to be "Arial". 18 18
My favorite font size is 8. 19 19
Don't worry. It isn't too small. Ok. Let's do another right-click, but this time select "Format Chart Area". 20 20
Here I'd like to remove the fill of the chart and its borderline. Click "No" for both. 21 21
The next step will be to render the gridlines less invasive. 22 22
Let's select the gridlines and do a right mouse click in order to select "Format Gridlines". 23 23
I'll choose to have a solid line, which is grey and is transparent at 75%. 24 24
I think gridlines look much better this way. 25 25
Let's adjust the size of the chart, as we want to have a quarter page chart. 26 26
We'll need to select a label for the horizontal axis. 27 27
I'll just click on the chart, choose "Select Data", go to the horizontal axis title section within the dialog box which opens, 28 28
and select the three years under analysis, as we have the breakdown of revenues for each of these years in the three columns. 29 29
Let's change the colors of the chart a little bit. I'll assign the following colors to each of the time series. See how by left clicking on just one of 30 30
them the whole time series was selected? This makes it easier for corrections and modifications. 31 31
"Net sales" will be in dark blue, 32 32
"Other revenues" will be in a lighter blue, 33 33
for "Recharges" we can have a distinctive color such as this version of red. 34 34
Now, let's make the columns of our chart a bit larger. In order to do this, I'll make a right-click on 35 35
one of the columns and select "Format Data Series". 36 36
Once I am in the "Format Data Series" menu, I'll reduce the size of the gap width from 150% 37 37
to 50%. 38 38
Ok. 39 39
What needs to be done yet? we have to deal with the "Gross Margin" percentage, which is represented as a line in the chart on the right. 40 40
That's why I'll select the data series of the "Gross Margin" percentage 41 41
and select again the "Format Data Series" menu. 42 42
Once we're inside the menu, 43 43
we have to click on "Secondary Axis". This will add a secondary axis to our chart, but does not solve the problem that we would like to see this 44 44
parameter as a line. In order to do that, I'll right- click on the time series and select "Change Series Chart Type", 45 45
where we can choose "line chart". 46 46
An additional detail would be to smooth the line representing "Gross Margin" percentage. 47 47
This can be done by selecting the line, right- clicking on it, and picking the "Format Data Series" menu. 48 48
Here we can find the "Line Style" menu, 49 49
in which we can click on "Smooth Line". 50 50
The last detail were missing is to add data labels to the values of our chart, which is done easily- 51 51
by right-clicking on a series and selecting "Add data lables". 52 52
The default labels are in black, but probably the better option is to have them in white and in bold. 53 53
I still need to remove the black line, which is on the axis. 54 54
Right-click on one axis, 55 55
select "Format Axis" 56 56
go to "Lined color" and remove the color by picking "No line". 57 57
The last detail that we have to address in order to render our two charts completely the same 58 58
is axis scale. 59 59
I'll adjust that by going to the axes options menu and manually selecting the scale settings, which I prefer. 60 60
Now the two charts are the same. 61 61
You can see how many manipulations are necessary, in order to render a chart professional and good-looking. I promise you 62 62
that the extra 63 63
effort is worth it, because respecting many of these little details shows a lot about your precision 64 64
and dedication.